package com.es.hr.baoxiao;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.util.ArrayList;


import com.es.business.area.AreaBean;
import com.es.business.area.AreaBiz;
import com.es.hr.emp.EmpInfoBean;
import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class BaoxiaoBiz {
	public BaoxiaoBean getBaoxiaoBaseInfoFromEmp(String loginId) throws Exception{
		String sql = "select emp_code, emp_name, emp_city_code, emp_city_value, emp_rtm_code, emp_rtm_name from hrm_emp_info where del_flag = 'N' and emp_id = ?";
		Dao dao = new Dao();
		BaoxiaoBean bean = new BaoxiaoBean();
		ArrayList<String> query = new ArrayList<String>();
		query.add(loginId);
		try {
			ResultSet rs = dao.Select(sql, query);
			while(rs.next()){
			bean.ctm_code = rs.getString(1);
			bean.ctm_name = rs.getString(2);
			bean.city_code = rs.getString(3);
			bean.city_value = rs.getString(4);
			bean.rtm_code = rs.getString(5);
			bean.rtm_name = rs.getString(6);
			}
		}catch(Exception e){
			throw e;
		}finally{
			dao.close();
		}
		return bean;	
	}
	
	public String getShiyebuById(String loginId) throws Exception{
		String sql = "select emp_shiyebu from hrm_emp_info where del_flag = 'N' and emp_id = ?";
		Dao dao = new Dao();
		String result = "";
		ArrayList<String> query = new ArrayList<String>();
		query.add(loginId);
		try {
			ResultSet rs = dao.Select(sql, query);
			while(rs.next()){
				result = rs.getString(1);
			}
		}catch(Exception e){
			throw e;
		}finally{
			dao.close();
		}
		return result;	
	}

	public boolean hasBaoxiaothisMouth(String ctm_code, String startTime, String endTime)  throws Exception{
		// TODO Auto-generated method stub
		String sql = "select count(*) from hrm_baoxiao_detail where del_flag = 'N' and ctm_code = ? and baoxiao_time > ? and baoxiao_time < ?";
		Dao dao = new Dao();
		boolean result = false;
		ArrayList<String> query = new ArrayList<String>();
		query.add(ctm_code);
		query.add(startTime);
		query.add(endTime);
		try{
			ResultSet rs = dao.Select(sql, query);
			while(rs.next())
				result = rs.getString(1).equals("0")?false:true;
		}catch(Exception e){
			throw e;
		}finally{
			dao.close();
		}
		return result;
	}

	public int getBaoxiaoIndexThisMouth(String ctm_code, String start,
			String end) throws Exception{
		// TODO Auto-generated method stub
		String sql = "select Max(baoxiao_index) from hrm_baoxiao_detail where del_flag = 'N' and ctm_code = ? and baoxiao_time > ? and baoxiao_time < ?";
		Dao dao = new Dao();
		int result = 1;
		ArrayList<String> query = new ArrayList<String>();
		query.add(ctm_code);
		query.add(start);
		query.add(end);
		try{
			ResultSet rs = dao.Select(sql, query);
			while(rs.next()){
				if(rs.getString(1) != null)
					result = new Integer(rs.getString(1)).intValue() + 1;
				else result = 1;
			}
		}catch(Exception e){
			throw e;
		}finally{
			dao.close();
		}
		return result;
	}

	public boolean insertBaoxiao(BaoxiaoBean bean)  throws Exception{
		// TODO Auto-generated method stub
		String sql = "insert into hrm_baoxiao_detail ( ";
		String values = ") values(";
		String end = ")";
		Dao dao = new Dao();
		ArrayList<String> query = new ArrayList<String>();
		Field[] fds = Class.forName("com.es.hr.baoxiao.BaoxiaoBean").getDeclaredFields(); 
		for(int i = 0; i < fds.length; ++i){
			if(fds[i].getName().equals("baoxiao_detail_id")) continue;
			Object o = fds[i].get(bean);
			if(o instanceof Float){
				query.add(new Float((Float) o).toString());
				sql += fds[i].getName() + ",";
				values += "?,";
			}
			else if(o instanceof Integer){
				query.add(new Integer((Integer) o).toString());
				sql += fds[i].getName() + ",";
				values += "?,";
			}else if(o == null){
			}else{
				query.add(new String((String) o).toString());
				sql += fds[i].getName() + ",";
				values += "?,";
			}
		}
		sql = sql + " del_flag " + values + "?" + end;
		query.add("N");
		try{
			dao.update(sql, query);
			return true;
		}catch(Exception e){
			throw e;
		}finally{
			dao.close();
		}
	}
	
	ArrayList<BaoxiaoBean> getBaoxiaoDetail(String ctm_code, String baoxiao_create_index, String baoxiao_create_time) throws Exception{
		ArrayList<BaoxiaoBean> baoxiaoBeans = new ArrayList<BaoxiaoBean>();
		Dao dao = new Dao();
		String sql = "select * from hrm_baoxiao_detail where del_flag = 'N' and ctm_code = ? and baoxiao_index = ? and baoxiao_time = ? ";
		try{
		ArrayList<String> query = new ArrayList<String>();
		query.add(ctm_code);
		query.add(baoxiao_create_index);
		query.add(baoxiao_create_time);
		
		ResultSet rs = dao.Select(sql, query);
		Field[] fds = Class.forName("com.es.hr.baoxiao.BaoxiaoBean").getDeclaredFields(); 
		while(rs.next()){
			BaoxiaoBean bean = new BaoxiaoBean();
			baoxiaoBeans.add(bean);
			for(int i = 0; i < fds.length; ++i){
				if(fds[i].getName().equals("baoxiao_index"))
					fds[i].set(bean, rs.getInt(fds[i].getName()));
				else if(fds[i].getName().equals("fee"))
					fds[i].set(bean, rs.getFloat(fds[i].getName()));
				else fds[i].set(bean, rs.getString(fds[i].getName()));
			}
		}
		}catch(Exception e){
			throw e;
		}finally{
			dao.close();
		}
		return baoxiaoBeans;
	}

	public void deleteBaoxiao(String ctm_code, String baoxiao_index, String start, String end) throws Exception{
		// TODO Auto-generated method stub
		Dao dao = new Dao();
		try{
			String sql = "delete from hrm_baoxiao_detail where ctm_code = ? and baoxiao_index = ? and baoxiao_time > ? and baoxiao_time < ?";
			ArrayList<String> param = new ArrayList<String>();
			param.add(ctm_code);
			param.add(baoxiao_index);
			param.add(start);
			param.add(end);
			dao.update(sql, param );
		}catch(Exception e){
			throw e;
		}finally{
			dao.close();
		}
	}
	
	public ArrayList<String> getEmpMailByQuanxianAndShiyebu(String role,
			String shiyebu) throws Exception {
		// TODO Auto-generated method stub
		ArrayList<String> result = new ArrayList<String>();
		Dao dao = new Dao();
		try{
			String sql = "select emp_mail from hrm_emp_info where emp_shiyebu = ? and emp_role = ?";
			ArrayList<String> param = new ArrayList<String>();
			param.add(shiyebu);
			param.add(role);
			ResultSet rs = dao.Select(sql, param );
			while(rs.next()){
				result.add(rs.getString(1));
			}
		}catch(Exception e){
			throw e;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public String getEmpMailById(String id) throws Exception {
		// TODO Auto-generated method stub
		String result ="";
		Dao dao = new Dao();
		try{
			String sql = "select emp_mail from hrm_emp_info where emp_id = ? ";
			ArrayList<String> param = new ArrayList<String>();
			param.add(id);
			ResultSet rs = dao.Select(sql, param );
			while(rs.next()){
				result = rs.getString(1);
			}
		}catch(Exception e){
			throw e;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public String getEmpMailByCode(String rtm_code) throws Exception {
		// TODO Auto-generated method stub
		String result ="";
		Dao dao = new Dao();
		try{
			String sql = "select emp_mail from hrm_emp_info where emp_code = ? ";
			ArrayList<String> param = new ArrayList<String>();
			param.add(rtm_code);
			ResultSet rs = dao.Select(sql, param );
			while(rs.next()){
				result = rs.getString(1);
			}
		}catch(Exception e){
			throw e;
		}finally{
			dao.close();
		}
		return result;
	}
}
